#!/usr/bin/perl -w
#
# @param $1 install home
# @param $2 delimiter
# @param $3 host
# @param $4 port
# @param $5 username
# @param $6 password
#
# -- 06/18/2009 Created by Zhixin Li
# -- Used to collect mysql slow log analyzed by mysqldumpslow 
# -- can check all mysqld version slow log. 06/25/2009, modified by Zhixin Li
# -- added collection time  07/06 2009, modified by Zhixin Li
# -- ~oracle/.mysqlslow_$PORT.log recording last rownum analyzed
# -- /tmp/slowloginc_$PORT.tmp recording new slowlog content from last analysis
# -- added mysql version judgement   08/11/2009 modified by Zhixin Li
# -- added row limit, each time read up to 200000 lines     08/11/2009  modified by Zhixin Li
use strict;

my ($MYSQL_HOME,$DELIM,$HOST,$PORT,$USER,$PASS) = @ARGV;
my $PARAM="";
$PARAM="$PARAM -h\"$HOST\"" if (defined($HOST) && $HOST ne "");
$PARAM="$PARAM -P\"$PORT\"" if (defined($PORT) && $PORT ne "");
$PARAM="$PARAM -u\"$USER\"" if (defined($USER) && $USER ne "");
$PARAM="$PARAM -p\"$PASS\"" if (defined($PASS) && $PASS ne "");
my $MYSQL="$MYSQL_HOME/bin/mysql";
my $MYSQLADMIN="$MYSQL_HOME/bin/mysqladmin";
if ($^O =~ m/MSWin32/) {
  $MYSQL =~ s/\//\\/g;
  $MYSQLADMIN =~ s/\//\\/g;
}
# Check if mysqld is alive
my $retmsg=`$MYSQLADMIN $PARAM ping 2>&1`;
chomp($retmsg);
if ( "$retmsg" ne "mysqld is alive" ) {
    print "em_error=Status${DELIM}Down\n";
    exit 1;
}

########### check if log_slow_queries is open #################
my $slowstat =`$MYSQL_HOME/bin/mysql -s $PARAM --skip-column-names -e "show variables like 'log_slow_queries'"`;
my $slowset = substr($slowstat,index($slowstat,"\t")+1,length($slowstat)-index($slowstat,"\t")+1);
chomp($slowset);
if ( "$slowset" ne 'ON' ) {
#    print "slow query log not ON\n";
    exit 0;
}

my $version =`$MYSQL_HOME/bin/mysql -s $PARAM --skip-column-names -e "show variables like 'version'"`;
chomp($version);
if ($version =~ /^version[\s+\t](\d).(\d).(\d+)\D/) {
    $version = $1.$2.$3;
}

###########  get mysql slow log file #####################
my $slowlog;
sub defaultslow {
  my $name = `/bin/hostname`;
  my $grep = grep(/\./,$name);
  my $dir=`$MYSQL_HOME/bin/mysql -N -s $PARAM --batch -e "show variables like 'datadir'"`;
  my $datadir = substr($dir,index($dir,"\t")+1,length($dir)-index($dir,"\t")+1);
  chomp($datadir);
  if ( $grep == 0 ) {
      $slowlog = "$datadir$name-slow.log";
  } else {
      my $position = index($name,'.',0);
      my $log = substr($name,0,index($name,"."));
      $slowlog = "$datadir$log-slow.log";
  }
}

sub getslowlog {
 my $ostype = `/bin/uname`;
 my $pidfile=`$MYSQL_HOME/bin/mysql -N -s $PARAM --batch -e "show variables like 'pid_file'"`;
 my $file = substr($pidfile,index($pidfile,"\t")+1,length($pidfile)-index($pidfile,"\t")+1);
 my $mypid;
 my $proc;

 chomp($file);
 open (FILE, "$file") or die "Couldn't open $file for reading: $!\n"; 
 $/=""; 
 $mypid = <FILE>; 
 close(FILE); 
 $/="\n";
 chomp($mypid);
 chomp($ostype);
 if ($ostype eq "SunOS") {
     $proc =`/usr/ucb/ps awwx|/bin/grep -v grep|/bin/grep mysqld|/bin/grep -w "$mypid "`;
 } else {
     $proc =`/bin/ps awwx|/bin/grep -v grep|/bin/grep mysqld|/bin/grep -w "$mypid "`;
 }

 my $mydef;
 my $cmd= substr($proc,index($proc,"/"),length($proc));
 my $grep;
 my @pparam;
 my @param;
 my @fields;
 $grep = grep(/--defaults-file/,$cmd);
   if ( $grep == 0) {
      $mydef =`$MYSQL_HOME/bin/mysqld --print-defaults|/bin/grep -v arguments`;
      if ($version < 5129) {
         $grep = grep(/--log.*slow.*queries=/,$mydef);
         if ( $grep == 0) {
             defaultslow();
         } else {
             @param = split (/ +/,$mydef);
             foreach (@param) {
                if (/^--log.*slow.*queries=/) {
                    @fields = split(/=/,$_);
                    $slowlog = $fields[1];
                }  
             }
         }
      } else {
         $grep = grep(/--slow.*query.*log.*file=/,$mydef);
         if ( $grep == 0) {
             defaultslow();
         } else {
             @param = split (/ +/,$mydef);
             foreach (@param) {
                if (/^--slow.*query.*log.*file=/) {
                    @fields = split(/=/,$_);
                    $slowlog = $fields[1];
                }
             }
         }
      }
   } else {
        @pparam = split (/ +/, $cmd);
        foreach (@pparam) {
           if (/^--defaults-file/) {
              $mydef = `$MYSQL_HOME/bin/mysqld $_ --print-defaults|/bin/grep -v arguments`;
              if ($version < 5129) {
                 $grep = grep(/--log.*slow.*queries=/,$mydef);
                 if ( $grep == 0) {
                     defaultslow();
                 } else {
                     @param = split (/ +/,$mydef);
                     foreach (@param) {
                        if (/^--log.*slow.*queries=/) {
                           @fields = split(/=/,$_);
                           $slowlog = $fields[1];
                        }  
                     }
                 }
              } else {
                $grep = grep(/--slow.*query.*log.*file=/,$mydef);
                if ( $grep == 0) {
                   defaultslow();
                } else {
                   @param = split (/ +/,$mydef);
                   foreach (@param) {
                       if (/^--slow.*query.*log.*file=/) {
                       @fields = split(/=/,$_);
                       $slowlog = $fields[1];
                       }
                   }
                }
              }
           }
        }
   }
}
getslowlog();
chomp($slowlog);
################ record slow log name ####################
my $slowlogname = "/tmp/slowlogname_$PORT.tmp";
open(OUTPUT, ">$slowlogname") or die "Couldn't open $slowlogname for writing: $!\n";
print OUTPUT ("$slowlog\n");

if ( ! -e "$slowlog" ) {
#    print "$slowlog is not exist.\n";
    exit 0;
}
my @line;
my ($sec,$min,$hour,$day,$month,$year) = localtime;
my $count;
my $times;
my $ttimes;
my $lock;
my $tlock;
my $rows;
my $trows;
my $concat = ' ';
my $query;
my $host;
########### check if .mysqlslow_$PORT.log exists, if exists, get the lastposition of slowlog ###########
my $lastlogpos = "$ENV{HOME}/.mysqlslow_$PORT.log";
my $lastpos = 0;
if (-s $lastlogpos) {
    open(MYFILE,"$lastlogpos") or die "Couldn't open $lastlogpos for reading: $!\n";
    $lastpos = <MYFILE>; 
    chomp($lastpos);
    close(MYFILE);
}

#my $cmd = `wc -l < $myslowlog`;
#chomp($cmd);
#my ($currow, $logname)=split(' ',$cmd);
################## count current slowlog and determine the incremental lines, then established tmpfile ############
my $currow;
my $tmpslowinc = "/tmp/slowloginc_$PORT.tmp";
open(FILE, "<", $slowlog) or die "can't open $slowlog: $!\n";
$currow += tr/\n/\n/ while sysread(FILE, $_, 2 ** 20);
close(FILE);
my $lineinc = $currow - $lastpos;
if ( $lineinc <= 0) {
  # print "no new slowlog\n";
    exit 0;
}

if ($currow > $lastpos + 200000) {
    $lastpos += 200000;
    system("head -$lastpos $slowlog|tail -200000 > $tmpslowinc");
    open(OUTFILE,">$lastlogpos") or die "Couldn't open $lastlogpos for writing: $!\n";
    print OUTFILE ("$lastpos\n");
} else {
    $lastpos = $currow - $lastpos;
    system("tail -$lastpos $slowlog > $tmpslowinc");
    open(OUTFILE,">$lastlogpos") or die "Couldn't open $lastlogpos for writing: $!\n";
    print OUTFILE ("$currow\n");
} 
#system("tail -$lineinc $slowlog  > $tmpslowinc");
################# collect mysqldumpslow result ###################################
my $rownum =0;
my @KV=`$MYSQL_HOME/bin/mysqldumpslow $tmpslowinc`;
for (@KV) {
    if (/^Count:/) {
         chomp;
         @line = split / +/, $_;
         $count = $line[1];
         $times = substr($line[2],5,length($line[2])-6);
         $ttimes = substr($line[3],1,length($line[3])-3);
         $lock = substr($line[4],5,length($line[4])-6);
         $tlock = substr($line[5],1,length($line[5])-3);
         $rows = substr($line[6],5,length($line[6])-5);
         $trows = substr($line[7],1,length($line[7])-3);
         $host = $line[8];
     } elsif (! /^\s*$/ && ! /^Count:/) {
         chomp;
         $query = $_;
         #$query=~ tr/\015//d;
         $query=~ s/\r//g;
         $concat .= $query;
     } else { 
           if ( $count != 0 ) {
              $rownum++;
              #print "em_result=$rownum${DELIM}$count${DELIM}$times${DELIM}$ttimes${DELIM}$lock${DELIM}$tlock${DELIM}$rows${DELIM}$trows${DELIM}$host${DELIM}$concat\n";
              printf("em_result=%04d-%02d-%02d $hour:$min:$sec${DELIM}$rownum${DELIM}$count${DELIM}$times${DELIM}$ttimes${DELIM}$lock${DELIM}$tlock${DELIM}$rows${DELIM}$trows${DELIM}$host${DELIM}$concat\n",$year+1900,$month+1,$day,$hour,$min,$sec,$rownum,$count,$times,$ttimes,$lock,$tlock,$rows,$trows,$host,$concat);
              $count = 0;
              $concat = ' ';
           }
     }
} 
#open(OUTFILE,">$lastlogpos") or die "Couldn't open $lastlogpos for writing: $!\n";
#print OUTFILE ("$currow\n");
exit 0;
